many users have encountered Excel file continuously in the course of fertilization, the phenomenon of growing insensitivity, corrupted files or even the terrible events of important data is lost. If it is because a large number of data input, then the file size increase is also beyond reproach, this time table structure should be optimized to save data to multiple workbooks distributed file method to reduce the size of individual files. However, in many cases, these have "bloated" figure workbook file which only a small amount of data, then it is how "fat" up?

This technique will be listed in the Excel file sizes of some common causes and treatment methods inflated.

There are a lot of work table a small graphic object

Worksheet If there is a large number of small graphical objects, then the file size may be in the user not informed explosion, this is a very common "Excel obesity." You can use the following two methods to check the Excel worksheet, the existence of such symptoms.

(1) The transfer button on the worksheet in a "position" dialog box, click the "positioning conditions" button in "Orientation conditions" dialog box, select the "object" item, click "OK", shown in Figure 1. Then observe whether the worksheet will display many of the selected object.

if the workbook contains multiple worksheets, each worksheet needs to used this method to find. On the "positioning" function more tips, see the tips 28.

Note: The hidden columns or rows of objects using this method can not be seen. $ Show_page $

(2) workbook using VBA on the object count, see the work table in each number of objects actually exist, if this amount is unreasonable, they indicate a problem. Press to open VBA Editor window, click the menu "Insert" ¡ú "module" to insert a new module, by default, the "module 1", then the code window in the module 1 Enter the following code:

Sub CountShapes ()
Dim n As Double
Dim ws As Worksheet
Dim Content As String
For Each ws In Worksheets
n = ws.Shapes.Count

Content = Content & "Sheet" & ws.Name & "has" & n & "objects" & vbCrLf
Next
MsgBox Content
End Sub

Finally, press the F5 key to run this code, you can see the results. In the seemingly blank worksheet, there are a large number of graphic objects, shown in Figure 2.

There are a lot of objects, and users do not need them, you can use two methods to deal with:

(1) just the positioning method, when an object is selected in the state of all keys delete them.

(2) the use of macros in multiple sheets in a more accurate remove these unwanted objects. For example, you can only delete the required height and width are less than 14.25 pounds (0.5 cm) of the object. $ Show_page $

Delete only the active sheet the size of the object in a particular code:

Sub DelShapes ()
Dim sp As Shape, n
For Each sp In ActiveSheet.Shapes
If sp.Width <14.25 And sp.Height <14.25 Then
sp.Delete
n = n + 1
End If
Next sp
MsgBox "were deleted" & n & "objects"
End Sub

Delete all worksheets in a specific size of the object code:

Sub DelAllShapes ()
Dim ws As Worksheet
Dim sp As Shape
Dim n As Double
Dim Content As String
For Each ws In Worksheets
For Each sp In ws.Shapes
If sp.Width <14.25 And sp.Height <14.25 Then
sp.Delete
n = n + 1
End If
Next
Content = Content & "Sheet" & ws.Name & "deleted" & n & "objects" & vbCrLf
n = 0
Next
MsgBox Content
End Sub

Of these puzzling objects may have several causes. Copy the content from the page directly after the paste to the worksheet, but do not use Paste Special. Inadvertently used the drawing toolbar line tools or other drawing tools, unconsciously inserted in a worksheet or other small straight line graphic object, because size is very small, almost impossible to see in naked eye. Then they passed a copy of the cell produced a large number of small graphics objects. Inserted in a worksheet picture or other graphic objects, operations, in turn its height width set to 0 or very small value, generated by copying a large number of objects.

Row or column insert a drawing object, the object of the property to "position with the unit and change the size of the (default)" and then hide rows or columns, or set the row height or column width for the very small value, thus to insert the object can not be seen. Worksheet object is not visible attribute set (Visible = false), or object located lines and fill color and background color of the same, so the object can not be seen.

2 worksheet set in the larger region of the cell formatting or conditional formatting

Careful observation worksheet scroll bar, if the slider is small, and drag the slider down in the end, can reach large numbers of rows or columns marked, but the actual use of the worksheet to the area is very small, as shown in Figure 3 . This indicates a considerable area may be set to the cell formatting or conditional formatting, which has not been used in the cell, can have a huge impact file size.

Solution is as follows. Click to really need the line number of the next line, press the key combination, select all of the extra lines (you can enter the line number in the Name box, such as 2000:65536), click the menu "Edit" ¡ú "Clear ¡ú" format " (or all). Similarly, you can also remove redundant column format.

Sometimes, users do need to work the table for the default format of a blank area in order to increase the data for future use, but be sure to use the correct way, otherwise causing heavy area is less than the default formats for arousing file size.

If you need a line or a large range of single cell formatting settings, you can select the entire row or entire column format cells, not just part of the ranks of individual choice format. The former will not cause the problem file size inflated, which will increase the file size. To test the two Excel files which were on the A1: A65536 Format Cells and on the A: A Format Cells, aim for the A series format, but the final document more than 100 times the size difference.